package viv.karaoke.book;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class mySQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_SONG = "song";
  public static final String SONG_ID = "songId";
  public static final String SONG_NAME = "name";
  public static final String SONG_ARTIST = "artist";
  public static final String SONG_FIRST_SENTENCE = "firstSentence";
  public static final String SONG_LYRIC = "lyric";

  public static final String TABLE_USER = "user";
  public static final String USER_ID = "userId";
  public static final String USER_NAME = "name";

  public static final String TABLE_FAVOURITE = "favourite";
  public static final String FAVOURITE_ID = "favouriteId";
  public static final String FAVOURITE_USER_ID = "userId";
  public static final String FAVOURITE_SONG_ID = "songId";

  public static final String VIEW_FAVOURITE = "vwFavourite";
  public static final String VIEW_FAVOURITE_ID = "favouriteId";
  public static final String VIEW_FAVOURITE_USER_ID = "userId";
  public static final String VIEW_FAVOURITE_SONG_ID = "songId";
  public static final String VIEW_FAVOURITE_USER_NAME = "userName";
  public static final String VIEW_FAVOURITE_SONG_NAME = "songName";
  public static final String VIEW_FAVOURITE_SONG_ARTIST = "songArtist";

  private static final String DATABASE_NAME = "karaokeBook.db";
  private static final int DATABASE_VERSION = 1;
  private final Context myContext;

  public mySQLiteHelper(Context context) 
  {
	  super(context, DATABASE_NAME, null, DATABASE_VERSION);
	  this.myContext = context;
	  try 
	  {  
		  String destPath = "/data/data/" + myContext.getApplicationContext().getPackageName() + "/databases/" + DATABASE_NAME;
		  
		  //Open your local DB as the input stream
		  InputStream myInput = myContext.getAssets().open(DATABASE_NAME);
		
		  //Open the empty DB as the output stream
		  OutputStream myOutput = new FileOutputStream(destPath);
		     
		  
		  boolean isEnableCopy = true;		  
		  File f = null;
		  try
		  {
			  f = new File(destPath);
		  }
		  catch (Exception ex )
		  {
			  ex.printStackTrace();
		  }
		  
		  SQLiteDatabase checkDB = null;
		  if (f.exists()) 
		  {			  
			  checkDB = SQLiteDatabase.openDatabase(destPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);			  
			  
			  if(checkDB != null)
			  {
				  int version = checkDB.getVersion();
				  if(DATABASE_VERSION == version)
				  {
					  isEnableCopy = false;
				  }
			  }
			   
			  if(checkDB != null){
				  checkDB.close(); 
			  }				
		  }
		  if(isEnableCopy)
		  {
			  this.getWritableDatabase();
			  CopyDB(myInput, myOutput);  
		  }
	  } 
  
	  catch (FileNotFoundException e) 
	  {
		  //throw new Error("Error file not found");
		  e.printStackTrace();		   
	  } 
	  
	  catch (IOException e) 
	  {
		  //throw new Error("Error copying database");
		  e.printStackTrace();
	  }
			    
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
    //database.execSQL(DATABASE_CREATE);    
	  int currentVersion = database.getVersion();
	  if(currentVersion < DATABASE_VERSION)
	  {
		  database.setVersion(DATABASE_VERSION);
	  }
  }
  
  public void CopyDB(InputStream inputStream, OutputStream outputStream) throws IOException 
  {
	  // ---copy 1K bytes at a time---
	  byte[] buffer = new byte[1024];
	  int length;
	  while ((length = inputStream.read(buffer)) > 0) {
	   outputStream.write(buffer, 0, length);
	  }
	  inputStream.close();
	  outputStream.close();
  }
  
  @Override
  public void onOpen(SQLiteDatabase db) {

  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(mySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    
    //db.execSQL("DROP VIEW IF EXISTS " + VIEW_FAVOURITE);
    //db.execSQL("DROP TABLE IF EXISTS " + TABLE_SONG);
    //db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
    //db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVOURITE);

    //onCreate(db);
  }

} 
